import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

object kun {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setAppName("theFirst")
      //.setMaster("local[*]")
    val spark = SparkSession
      .builder()
      .config(conf)
      .getOrCreate()

    spark.sparkContext.setLogLevel("warn")
    //course
    val frame = spark.read.option("delimiter", ",").schema("cid Int,cname String,tid Int").csv(args(0))
    frame.createOrReplaceTempView("course")
    spark.sql("select * from course").show()
    //score
    val frame1 = spark.read.option("delimiter", ",").schema("sid Int,cid Int,score Int").csv(args(1))
    frame1.createOrReplaceTempView("score")
    spark.sql("select * from score").show()
    //student
    val frame2 = spark.read.option("delimiter", ",").schema("sid Int,sname String,birthday String,gender String,cla String,kunkun String").csv(args(2))
    frame2.createOrReplaceTempView("student")
    spark.sql("select * from student").show()
    //teacher
    val frame3 = spark.read.option("delimiter", ",").schema("tid Int,tname String").csv(args(3))
    frame3.createOrReplaceTempView("teacher")
    //import spark.implicits._
    spark.sql("select * from teacher").show()
    //5)查询出三门科目的平均成绩，并将其代课老师打印到控制台
    println("查询出三门科目的平均成绩，并将其代课老师打印到控制台:")
    spark.sql(
      """
        |
        | select distinct
        | c.cid,c.cname,t.tname,
        | round(avg(sc.score) over(partition by c.cid),2) avg_sc
        | from course c,score sc,teacher t
        | where c.cid=sc.cid and t.tid=c.tid
        |
        |
        |
        |""".stripMargin).show()
    //查询所有学生信息，将性别进行转换：男->man 女->women
    println("查询所有学生信息，将性别进行转换：男->man 女->women")
    spark.sql(
      """
        |
        | select
        | s.sid,s.sname,s.birthday,
        | if(s.gender='男','man','women'),
        | s.cla,s.kunkun
        | from student s
        |
        |""".stripMargin).show()
    //7)查询出有任意两门成绩低于60的学生信息
    println("查询出有任意两门成绩低于60的学生信息")
    spark.sql(
      """
        |
        | select
        | *
        | from (select
        | t1.sid,t1.sname,count(*) num
        | from (select
        | s.sid,s.sname
        | from student s,score sc
        | where s.sid=sc.sid
        | and sc.score<60) t1
        | group by t1.sid,t1.sname) t2
        | where t2.num=2
        |
        |
        |
        |
        |""".stripMargin).show()
    //8)查询出语文成绩前3的学生信息及分数
    println("查询出语文成绩前3的学生信息及分数")
    spark.sql(
      """
        |
        | select
        | *
        | from (select
        | s.sid,s.sname,c.cname,
        | rank() over(partition by c.cid order by sc.score desc) num
        | from student s,course c,score sc
        | where s.sid=sc.sid and c.cid=sc.cid) t1
        | where t1.num<=3 and t1.cname='语文'
        |
        |
        |""".stripMargin).show()
    //9)查询出男生三科总分的平均分以及女生三科总分的平均分
    println("9)查询出男生三科总分的平均分以及女生三科总分的平均分")
    spark.sql(
      """
        |select t.gender,avg(t.sum_sc)
        |from (select s.sid,s.gender,sum(sc.score) sum_sc,avg(sc.score) avg_sc
        |from student s join score sc on s.sid = sc.sid
        |group by s.sid,s.gender) t
        |group by t.gender
        |""".stripMargin).show()

    spark.sql(
      """
        |
        | select
        | s.gender,
        | round(sum(sc.score)*1.0/3,2) avg_sc
        |
        | from student s
        | inner join score sc on sc.sid = s.sid
        | inner join course c on c.cid=sc.cid
        | group by s.gender
        |
        |""".stripMargin).show()
    //10)查询出哪位同学学过的科目数小于3
    println("查询出哪位同学学过的科目数小于3")
    spark.sql(
      """
        |
        | select
        | *
        | from (select
        | s.sid,s.sname,count(*) num
        | from student s,score sc
        | where s.sid=sc.sid
        | group by s.sid,s.sname) t1
        | where t1.num<3
        |
        |""".stripMargin).show()
    //查询和编号02教师同姓的同学信息以及总分
    println("查询和编号02教师同姓的同学信息以及总分")
    spark.sql(
      """
        |
        | select
        | *
        | from (select
        | s.sid,s.sname,sum(sc.score) sum_sc
        | from student s,score sc
        | where s.sid=sc.sid
        | group by s.sid,s.sname) t1
        | where substring(t1.sname,0,1) = (select substring(t.tname,0,1) from teacher t where t.tid=1002)
        |
        |
        |""".stripMargin).show()

    //查询编号03的教师所交科目中有哪些学生本科成绩小于60分
    spark.sql(
      """
        |
        | select
        | t.tid,sc.sid,sc.score
        | from teacher t,course c,score sc
        | where t.tid=c.tid and c.cid=sc.cid
        | and sc.score<60 and t.tid=1003
        |
        |""".stripMargin).show()


  }

}
